package com.haotian.db.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.haotian.dataconvert.Dto;
import com.haotian.dataconvert.impl.HashDto;

public class _JdbcTemplate {

	private _DBConnectionPool db;

	public _JdbcTemplate(_DBConnectionPool pool) {
		this.db = pool;
	}

	/**
	 * 执行非查询的sql语句
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public int execute(String sql, Object... args) throws SQLException {
		int count = 0;
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = db.getConnection();
			if (con != null) {
				ps = con.prepareStatement(sql);
				this.setValue(ps, args);
				count = ps.executeUpdate();
			}
		} finally {
			this.close(null, null, ps, con);
		}
		return count;
	}

	/**
	 * 使用PreparedStatement查询数据，返回List集合
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public List<Dto<String, Object>> queryForList(String sql, Object... args) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Dto<String, Object>> dtos = null;
		Dto<String, Object> dto = null;
		try {
			con = db.getConnection();
			if (con != null) {
				ps = con.prepareStatement(sql);
				this.setValue(ps, args);
				rs = ps.executeQuery();
				ResultSetMetaData rsmd = rs.getMetaData();// 获得表结构
				int rowCnt = rsmd.getColumnCount();// 列数
				dtos = new ArrayList<Dto<String, Object>>();
				while (rs.next()) {// 循环每行
					dto = new HashDto<String, Object>();
					for (int i = 1; i <= rowCnt; i++) {// 本行的每列 从1开始
						dto.put(rsmd.getColumnName(i), rs.getObject(i));
					}
					dtos.add(dto);
				}
			}
		} finally {
			this.close(rs, null, ps, con);
		}
		return dtos;
	}

	/**
	 * 设置 PreparedStatement 参数
	 * 
	 * @param ps
	 * @param args
	 *            Object[] arg0,arg1,arg2,arg3...
	 * @throws SQLException
	 */
	public void setValue(PreparedStatement ps, Object... args) throws SQLException {
		if (args == null || args.length == 0) {
			return;
		}

		Object tmp = null;
		for (int i = 0; i < args.length; i++) {
			tmp = args[i];
			if (tmp instanceof String) {
				ps.setString((i + 1), (String) tmp);
			} else if (tmp instanceof Integer) {
				ps.setInt((i + 1), (Integer) tmp);
			}
		}
	}

	/**
	 * 关闭方法
	 * 
	 * @param rs
	 * @param st
	 * @param ps
	 * @param con
	 * @throws SQLException
	 */
	public void close(ResultSet rs, Statement st, PreparedStatement ps, Connection con) throws SQLException {
		if (rs != null) {
			rs.close();
		}
		if (st != null) {
			st.close();
		}
		if (ps != null) {
			ps.close();
		}
		if (con != null) {
			con.close();
		}
	}
}
